package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashPaidToIndividualDataList;
import com.mbc.rmt.entities.CashPaidToIndividual;
import com.mbc.rmt.entities.CashPaidToIndividualCri;
import com.mbc.rmt.entities.CashPaidToIndividualRpt;

public class CashPaidToIndividualDAOImpls extends DAO<CashPaidToIndividual> implements CashPaidToIndividualDAO{
	public final String field_paidamount = "paidedamount";
	public final String field_status = "status";
	public final String field_tstatus = "tstatus";
	
	
	public CashPaidToIndividualDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "cashpaidtoindividual";
		
		
	}

	@Override
	public void create(CashPaidToIndividual entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "insert into "
				+ table_name
				+ " ( PaymentID, PaymentNO, CreatedDate, ModifiedDate, TrDate, CreatedBy, ModifiedBy, FromAgentId," +
				" FromBranchID, DefaultBranchID, ReceiptNo, AddCode, Sender, Receiver, NRC, Address, CurrencyId, Rate," +
				" Amount, ExtraFees, BankId,ReceiptDetailID,ReceivedID) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getPaymentID());
		pstmt.setString(i++, entity.getPaymentno());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getTrdate().getTime()));
		if(entity.getCreatedBy()!=null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getModifiedBy()!=null)
			pstmt.setLong(i++, entity.getModifiedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getFromagentID()!=null)
			pstmt.setLong(i++, entity.getFromagentID());
		else
			pstmt.setString(i++, null);
		if(entity.getFrombranchID()!=null)
			pstmt.setLong(i++, entity.getFrombranchID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getDefaultbranchID());
		pstmt.setString(i++, entity.getReceiptno());
		pstmt.setString(i++, entity.getAddcode());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNRC());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setLong(i++, entity.getCurrencyID());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		if(entity.getBankID()!=null)
			pstmt.setLong(i++, entity.getBankID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getReceiptdetailid());
		pstmt.setLong(i++, entity.getReceiptid());
		
		
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(CashPaidToIndividual entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "update "
				+ table_name
			    + " set PaymentID = ?, PaymentNO = ?, CreatedDate = ?, ModifiedDate = ?, TrDate = ?, CreatedBy = ?," +
				" ModifiedBy = ?, FromAgentId = ?, FromBranchID = ?, DefaultBranchID = ?, ReceiptNo = ?, AddCode = ?," +
				" Sender = ?, Receiver = ?, NRC = ?,Address = ?,  CurrencyId = ?, Rate = ?, Amount = ?,ExtraFees = ?, BankId = ?, ReceiptDetailID= ?, ReceivedID = ?"
				+ " where PaymentId = " + entity.getPaymentID();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getPaymentID());
		pstmt.setString(i++, entity.getPaymentno());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getTrdate().getTime()));
		if(entity.getCreatedBy()!=null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getModifiedBy()!=null)
			pstmt.setLong(i++, entity.getModifiedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getFromagentID()!=null)
			pstmt.setLong(i++, entity.getFromagentID());
		else
			pstmt.setString(i++, null);
		if(entity.getFrombranchID()!=null)
			pstmt.setLong(i++, entity.getFrombranchID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getDefaultbranchID());
		pstmt.setString(i++, entity.getReceiptno());
		pstmt.setString(i++, entity.getAddcode());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNRC());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setLong(i++, entity.getCurrencyID());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		if(entity.getBankID()!=null)
			pstmt.setLong(i++, entity.getBankID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getReceiptdetailid());
		pstmt.setLong(i++, entity.getReceiptid());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(CashPaidToIndividual entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "DELETE FROM "+table_name+" WHERE paymentId = "+entity.getPaymentID();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashPaidToIndividual find(long key) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividual l_CashPaidToIndividual = null;
		query = getSelectQuery() + " where paymentId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CashPaidToIndividual = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CashPaidToIndividual;
	}

	@Override
	public CashPaidToIndividual getCashPaidToIndividual(
			CashPaidToIndividual entity) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividual l_CashPaidToIndividual = null;
//		query = "SELECT  cpi.PaymentID,cpi.PaymentNo,cpi.TRDate,cpi.FromAgentID,cpi.FromBranchID, "+ 
//				"cpi.ReceiptNo,cpi.AddCode,cpi.Sender,cpi.Receiver,cpi.NRC,cpi.Address,cpi.BankID, "+
//				"cpi.CurrencyId,cpi.Rate,cpi.Amount,cpi.ExtraFees,cur.CurrencyCode, "+ 
//				"cr.currencyratecode,cr.CurrencyRateId,cpi.ReceiptDetailID,cpi.ReceivedID, "+ 
//				"ISNULL(ag.agent_code,'')agent_code,bh.BranchCode,ISNULL(bk.BankCode,'')BankCode,cpi.DefaultBranchID,cr.CurrencyRateId,cr.CurrencyRateCode " +
//				"from CashPaidToIndividual cpi "+ 
//				"LEFT JOIN Agent ag ON ag.agent_id = cpi.FromAgentID " +
//				"LEFT JOIN Branch bh ON bh.BranchId = cpi.FromBranchID " +
//				"LEFT JOIN Currency cur ON cur.CurrencyId = cpi.CurrencyID " +
//				"LEFT JOIN Bank bk ON bk.BankId = cpi.BankID "+
//				"LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpi.CurrencyID AND " +
//				"cr.convertcurrencyid=(SELECT HomeCurrencyid FROM Setup) "+
//				" where PaymentNo = '"+entity.getPaymentno()+"' " +
//				" and cpi.DefaultBranchID  = " + entity.getDefaultbranchID()+
//				" And cr.branchid = "+ entity.getDefaultbranchID();
		query = " DECLARE @homecurrencyid bigint"+
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				" select row_number() over(order by PaymentID) as [rowno], cpi.PaymentID,cpi.PaymentNo,cpi.TRDate,cpi.FromAgentID,cpi.FromBranchID," +
				" cpi.ReceiptNo,cpi.AddCode,cpi.Sender,cpi.Receiver,cpi.NRC,cpi.Address,cpi.ReceiptDetailID,cpi.ReceivedID," +
				" cpi.CurrencyId,cpi.Rate,cpi.Amount,cpi.ExtraFees,cpi.BankID,c.CurrencyCode," +
				" ISNULL(ag.agent_code,'')agent_code,bh.BranchCode,ISNULL(bk.BankCode,'')BankCode ,cpi.DefaultBranchID," +
				" ISNULL(cr.currencyrateid,0) currencyrateid," +
				" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cpi.CurrencyID" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode" +
				" from CashPaidToIndividual cpi" +
				" LEFT JOIN Agent ag ON ag.agent_id = cpi.FromAgentID" +
				" LEFT JOIN Branch bh ON bh.BranchId = cpi.FromBranchID" +
				" LEFT JOIN Currency c ON c.CurrencyId = cpi.CurrencyID" +
				" LEFT JOIN Bank bk ON bk.BankId = cpi.BankID" +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpi.CurrencyID" +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+entity.getBranchId()+
				" where (PaymentNo = '"+entity.getPaymentno()+"')";
				//"and cr.branchid  = " + entity.getDefaultbranchID();
		
				pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CashPaidToIndividual = readRecordsearch(rs);
		rs.close();
		pstmt.close();
		return l_CashPaidToIndividual;

	}

	@Override
	public CashPaidToIndividualDataList findCashPaidToIndividualList(
			CashPaidToIndividual cri) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividualDataList l_DataList = new CashPaidToIndividualDataList();
		ArrayList<CashPaidToIndividual> l_entityList = new ArrayList<CashPaidToIndividual>();
		String l_cri="";
//		String l_cri = ""//" and (PaymentNo like N'%" + cri.getKeyword() + "%')"
//				//+ " and  cpi.DefaultBranchID =" + cri.getBranchId()
//				" And cr.branchid = "+ cri.getBranchId();
		if(!cri.getFieldname().equals("TrDate"))
			l_cri = "and cpi." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'"+
					"AND cpi.DefaultBranchId =  "+cri.getBranchId();
		else
			l_cri = "and (Convert(varchar(100),cpi." + cri.getFieldname() + ", 103)) like N'%" + cri.getKeyword() + "%'"+
					"AND cpi.DefaultBranchId =  "+cri.getBranchId();
		query = " DECLARE @homecurrencyid bigint"+
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				" set rowcount 10 SELECT ### FROM (SELECT row_number()OVER(ORDER BY cpi.PaymentNo desc)rowno," +
				" cpi.PaymentID,cpi.PaymentNo,cpi.TRDate,cpi.FromAgentID,cpi.FromBranchID," +
				" cpi.ReceiptNo,cpi.AddCode,cpi.Sender,cpi.Receiver,cpi.NRC,cpi.Address,cpi.ReceiptDetailID,cpi.ReceivedID," +
				" cpi.CurrencyId,cpi.Rate,cpi.Amount,cpi.ExtraFees,cpi.BankID,c.CurrencyCode," +
				" ISNULL(ag.agent_code,'')agent_code,bh.BranchCode,ISNULL(bk.BankCode,'')BankCode ,cpi.DefaultBranchID," +
				" ISNULL(cr.currencyrateid,0) currencyrateid," +
				" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cpi.CurrencyID" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode" +
				" from CashPaidToIndividual cpi" +
				" LEFT JOIN Agent ag ON ag.agent_id = cpi.FromAgentID" +
				" LEFT JOIN Branch bh ON bh.BranchId = cpi.FromBranchID" +
				" LEFT JOIN Currency c ON c.CurrencyId = cpi.CurrencyID" +
				" LEFT JOIN Bank bk ON bk.BankId = cpi.BankID" +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cpi.CurrencyID" +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId()+
				" where 1 = 1 " + l_cri + ") cashpaidtoindividual ";
		
		String l_query = query.replace("###", "*");
		l_query += " where rowno > " + cri.getIndex();
			
		pstmt = conn.prepareStatement(l_query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecordsearch(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = " and (PaymentNo like N'%" + cri.getKeyword()
				+ "%')" + " and DefaultBranchID = " + cri.getBranchId();
		query = "select count(PaymentID) from " + table_name + " where 1 = 1"
			+ l_cri1;
		
		String l_query2 = query.replace("###", "count(*)");
		
		pstmt = conn.prepareStatement(l_query2);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	@Override
	public ArrayList<CashPaidToIndividual> getCashPaidToIndividualSearchlist(
			CashPaidToIndividual entity) throws SQLException {
		// TODO Auto-generated method stub
		ArrayList<CashPaidToIndividual> l_result = null;
		query = "SELECT cd.ReceiptDetailID,crba.ReceiptNo,ac.Code,cd.Sender,cd.Receiver,cd.NRC,cd.Address," +
				"cur.CurrencyId,cur.CurrencyCode,crba.TotalPaymentAmount,crba.ExtraFees" +
				" FROM CashRemittanceByAgent_Archive crba" +
				" INNER JOIN CashRemittanceByAgentDetails_Archive cd ON cd.ReceiptID = crba.ReceiptID" +
				" INNER JOIN AddCode ac ON ac.AddCodeId = cd.AddCodeID" +
				" INNER JOIN Agent ag ON ag.agent_id = crba.FromAgentID" +
				" LEFT JOIN Currency cur ON cur.CurrencyId = crba.PaymentCurrencyID" +
				" WHERE (crba.Status = 2)"+
//				" and crba.PaymentCurrencyID = "+entity.getCurrencyID()+
				" and cd.ToBranchID = "+ entity.getDefaultbranchID();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_result = new ArrayList<CashPaidToIndividual>();
			do {
				l_result.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_result;
	}
	
	@Override
	protected CashPaidToIndividual readRecord(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividual l_entity = new CashPaidToIndividual();
		l_entity.setPaymentID(rs.getLong("paymentID"));
		l_entity.setPaymentno(rs.getString("paymentno"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setTrdate(rs.getDate("trdate"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setFromagentID(rs.getLong("fromagentID"));
		l_entity.setFrombranchID(rs.getLong("frombranchID"));
		l_entity.setDefaultbranchID(rs.getLong("defaultbranchID"));
		l_entity.setReceiptno(rs.getString("receiptno"));
		l_entity.setAddcode(rs.getString("addcode"));
		l_entity.setSender(rs.getString("sender"));
		l_entity.setReceiver(rs.getString("receiver"));
		l_entity.setNRC(rs.getString("nRC"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setCurrencyID(rs.getLong("currencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setAmount(rs.getDouble("amount"));
		l_entity.setExtraFees(rs.getDouble("extraFees"));
		l_entity.setBankID(rs.getLong("bankID"));
		l_entity.setReceiptdetailid(rs.getLong("receiptdetailid"));
		l_entity.setReceiptid(rs.getLong("receivedid"));
		
		return l_entity;
		
	}
	
	protected CashPaidToIndividual readRecords(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividual l_entity = new CashPaidToIndividual();
		l_entity.setReceiptdetailid(rs.getLong("receiptdetailid"));
		l_entity.setReceiptid(rs.getLong("receivedid"));
		l_entity.setReceiptno(rs.getString("receiptno"));
		l_entity.setAddcode(rs.getString("code"));
		l_entity.setSender(rs.getString("sender"));
		l_entity.setReceiver(rs.getString("receiver"));
		l_entity.setNRC(rs.getString("nRC"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setCurrencyID(rs.getLong("currencyID"));
		l_entity.setCurrencycode(rs.getString("currencycode"));
		l_entity.setAmount(rs.getDouble("TotalPaymentAmount"));
		l_entity.setExtraFees(rs.getDouble("extraFees"));
		l_entity.setCurrencyratecode(rs.getString("currencyratecode"));
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setStatus(rs.getString("Status"));
		return l_entity;
		
	}
	
	protected CashPaidToIndividual readRecordsearch(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividual l_entity = new CashPaidToIndividual();
		l_entity.setPaymentID(rs.getLong("paymentID"));
		l_entity.setPaymentno(rs.getString("paymentno"));
		l_entity.setTrdate(rs.getDate("trdate"));
		l_entity.setFromagentID(rs.getLong("fromagentID"));
		l_entity.setFrombranchID(rs.getLong("frombranchID"));
		l_entity.setDefaultbranchID(rs.getLong("defaultbranchID"));
		l_entity.setReceiptno(rs.getString("receiptno"));
		l_entity.setAddcode(rs.getString("addcode"));
		l_entity.setSender(rs.getString("sender"));
		l_entity.setReceiver(rs.getString("receiver"));
		l_entity.setNRC(rs.getString("nRC"));
		l_entity.setAddress(rs.getString("address"));
		l_entity.setCurrencyID(rs.getLong("currencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setAmount(rs.getDouble("amount"));
		l_entity.setExtraFees(rs.getDouble("extraFees"));
		l_entity.setBankID(rs.getLong("bankID"));
		l_entity.setAgentcode(rs.getString("agent_code"));
		l_entity.setBranchcode(rs.getString("branchcode"));
		l_entity.setBankcode(rs.getString("bankcode"));
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setCurrencyrateid(rs.getLong("currencyrateid"));
		l_entity.setCurrencyratecode(rs.getString("currencyratecode"));
		l_entity.setReceiptdetailid(rs.getLong("receiptdetailid"));
		l_entity.setReceiptid(rs.getLong("receivedid"));
		
		
		return l_entity;
		
	}

	@Override
	public CashPaidToIndividualDataList findCashPaidToIndividualSearchlist(
			CashPaidToIndividual cri) throws SQLException {
		// TODO Auto-generated method stub
		CashPaidToIndividualDataList l_DataList = new CashPaidToIndividualDataList();
		ArrayList<CashPaidToIndividual> l_entityList = new ArrayList<CashPaidToIndividual>();
		 
		String l_cri = " and (ReceiptNo like N'%" + cri.getKeyword() + "%' " +
				   "OR Code like N'%" + cri.getKeyword() + "%'" +
				   "OR ac.Sender like N'%" + cri.getKeyword() + "%' " +
				   "OR Receiver like N'%" + cri.getKeyword() + "%' " +
				   "OR NRC like N'%" + cri.getKeyword() + "%'" +
				   "OR Address like N'%" + cri.getKeyword() + "%'" ;
			
		
		if (cri.getFromagentID()!=0)
		{	 	 
				 l_cri +=  "and DefaultBranchID = '"+cri.getBranchId()+"')";
				 if(cri.getFieldname().equals("ReceiptNo"))
						l_cri = " and cra." + cri.getFieldname() + " like N'%" +cri.getKeyword() + "%'";
					else
						{
						if(cri.getFieldname().equals("Code"))
							l_cri = " and ac." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
						else
							l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
						}
		}
		else {
				 l_cri +=  "and IssuedBranchId = '"+cri.getBranchId()+"')";
				 if(cri.getFieldname().equals("ReceiptNo"))
					{
					 l_cri = " and  " + cri.getFieldname() + " like N'%" +cri.getKeyword() + "%'";
					}
				else
					{
						if(cri.getFieldname().equals("Code"))
							l_cri = " and ac." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
						else
							l_cri = " and rcra." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
					}
					
			 }

		if (cri.getFromagentID()!=0)
			
			query=	 " DECLARE @homecurrencyid bigint"+
					 " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
					 " set rowcount 10 select * from (select row_number() over(order by cra.ReceiptNo) as [rowno]," +
					 " crad.ReceiptDetailID, 0 ReceivedId,cra.ReceiptNo,ac.Code,crad.Sender,crad.Receiver,crad.NRC,crad.Address," +
					 " c.CurrencyId,c.CurrencyCode,(crad.PaymentAmount-isnull(crad.paidedamount,0))TotalPaymentAmount,crad.ExtraFees,cra.Rate," +
					 " ISNULL(cr.currencyrateid,0) currencyrateid," +
					 " (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cra.PaymentCurrencyID" +
					 " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode,'' Status" +
					 " from CashRemittanceByAgent_Archive cra" +
					 " Inner Join CashRemittanceByAgentDetails_Archive crad on crad.ReceiptID = cra.ReceiptID and cra.DefaultBranchID = crad.ToBranchID" +
					 " INNER JOIN AddCode ac ON ac.AddCodeId = crad.AddCodeID" +
					 " INNER JOIN Currency c ON cra.PaymentCurrencyID=c.currencyid" +
					 " LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cra.PaymentCurrencyID" +
					 " AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId() +
					 " where 1 = 1 and crad.paymentamount <> isnull(crad.paidedamount,0) and  cra.Status = 2 and ISNULL(crad.Status,0)= 0 and ISNULL(crad.PaidedAmount,0) = 0" +
					 " AND cra.FromAgentID="+cri.getFromagentID()+
					 "  "+ l_cri + ") CashRemittanceByAgent_Archive" +
					 " where rowno > " + cri.getIndex();
		else
//			query=	" DECLARE @homecurrencyid bigint" +
//					" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
//					" set rowcount 10 select * from (select row_number() over(order by ReceiptNo) as" +
//					" [rowno],rcra.ReceivedId,0 ReceiptDetailID,rcra.ReceiptNo,ac.Code,rcra.Sender,rcra.Receiver,rcra.NRC,rcra.Address," +
//					" c.CurrencyId,c.CurrencyCode,(rcra.PaidAmount-isnull(rcra.paidedamount,0))TotalPaymentAmount,rcra.ExtraFees,rcra.Rate," +
//					" ISNULL(cr.currencyrateid,0) currencyrateid," +
//					" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=rcra.PaidCurrencyId" +
//					" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode" +
//					" from RemittanceCashReceivedArchive rcra" +
//					" INNER JOIN AddCode ac ON ac.AddCodeId = rcra.AddCodeID" +
//					" INNER JOIN Currency c ON rcra.PaidCurrencyId=c.currencyid" +
//					" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = rcra.PaidCurrencyId" +
//					" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId() +
//					" where 1 = 1 and rcra.PaidAmount <> isnull(rcra.paidedamount,0)   " + l_cri + ") RemittanceCashReceivedArchive " +
//					" where rowno > " + cri.getIndex();
			
		
//			DECLARE @homecurrencyid bigint SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup) 
//			select * from (select row_number() over(order by ReceiptNo) as [rowno],
//			0 ReceiptDetailID,rcra.ReceivedId,rcra.ReceiptNo,ac.Code,rcra.Sender,
//			rcra.Receiver,rcra.NRC,rcra.Address, c.CurrencyId,c.CurrencyCode,
//			(rcra.PaidAmount-isnull(rcra.paidedamount,0))TotalPaymentAmount,rcra.ExtraFees,rcra.Rate, 
//			ISNULL(cr.currencyrateid,0) currencyrateid, (CASE WHEN cr.currencyratecode 
//			IS NULL AND @homecurrencyid=rcra.PaidCurrencyId THEN c.currencycode+c.currencycode 
//			ELSE cr.currencyratecode END )currencyratecode,'RcrA' Status 
//			 from RemittanceCashReceivedArchive rcra
//			left Join AddCode ac ON ac.AddCodeId = rcra.AddCodeID 
//			left Join Currency c ON rcra.PaidCurrencyId=c.currencyid 
//			left Join CurrencyRate cr ON cr.SourceCurrencyid = rcra.PaidCurrencyId
//			AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = 201407130000003911 
//			where Status= 2 and isnull(TStatus,0)=0 
//			and isnull(PaidedAmount,0)=0 and IssuedBranchId = 201407130000003911
//			) RemittanceCashReceivedArchive 

			
			
			
			query=	" DECLARE @homecurrencyid bigint" +
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				" set rowcount 10 select * from (select row_number() over(order by ReceiptNo) as" +
				" [rowno],0 ReceiptDetailID,rcra.ReceivedId,rcra.ReceiptNo,ac.Code,rcra.Sender,rcra.Receiver,rcra.NRC,rcra.Address," +
				" c.CurrencyId,c.CurrencyCode,(rcra.PaidAmount-isnull(rcra.paidedamount,0))TotalPaymentAmount,rcra.ExtraFees,rcra.Rate," +
				" ISNULL(cr.currencyrateid,0) currencyrateid," +
				" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=rcra.PaidCurrencyId" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode,'RcrA' Status" +
				" from RemittanceCashReceivedArchive rcra" +
				" left Join AddCode ac ON ac.AddCodeId = rcra.AddCodeID" +
				" left Join Currency c ON rcra.PaidCurrencyId=c.currencyid" +
				" left Join CurrencyRate cr ON cr.SourceCurrencyid = rcra.PaidCurrencyId" +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId() +
				
				" where 1 = 1 and rcra.PaidAmount <> isnull(rcra.paidedamount,0)" +
				" AND rcra.BranchId="+cri.getFrombranchID()+ 
				" and rcra.Status = 2  and ISNULL(rcra.TStatus,0)= 0 and ISNULL(rcra.PaidedAmount,0) = 0 and rcra.IssuedBranchId="+cri.getBranchId()+ l_cri + ") RemittanceCashReceivedArchive " +
				" where rowno > " + cri.getIndex() +
				" Union " +
				" select * from (select row_number() over(order by ReceiptNo) as [rowno]," +
				" crad.ReceiptDetailID, 0 ReceivedId,cra.ReceiptNo,ac.Code,crad.Sender,crad.Receiver,crad.NRC,crad.Address," +
				" c.CurrencyId,c.CurrencyCode,(crad.PaymentAmount-isnull(crad.paidedamount,0))TotalPaymentAmount,crad.ExtraFees,cra.Rate," +
				" ISNULL(cr.currencyrateid,0) currencyrateid," +
				" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=cra.PaymentCurrencyID" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode,'CraA' Status" +
				" from CashRemittanceByAgent_Archive cra" +
				" Inner Join CashRemittanceByAgentDetails_Archive crad on crad.ReceiptID = cra.ReceiptID " +
//				"and cra.DefaultBranchID = crad.ToBranchID" +
				" INNER JOIN AddCode ac ON ac.AddCodeId = crad.AddCodeID" +
				" INNER JOIN Currency c ON cra.PaymentCurrencyID=c.currencyid" +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cra.PaymentCurrencyID" +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId()+
				" where 1 = 1 and crad.paymentamount <> isnull(crad.paidedamount,0) " +
	//			" AND crad.ToBranchID="+cri.getFrombranchID()+
				" AND cra.DefaultBranchID="+cri.getFrombranchID()+
				" and  cra.Status = 2 and ISNULL(crad.Status,0)= 0 and ISNULL(crad.PaidedAmount,0) = 0"+ l_cri + ") CashRemittanceByAgent_Archive" +
				" where rowno > " + cri.getIndex(); 
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
					l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		if (!cri.getAgentcode().equals(""))
		query = "select count(cra.ReceiptID) from CashRemittanceByAgent_Archive cra" +
				" INNER JOIN CashRemittanceByAgentDetails_Archive cd ON" +
				" cd.ReceiptID = cra.ReceiptID and cra.DefaultBranchID = cd.ToBranchID" +
				" INNER JOIN AddCode ac ON ac.AddCodeId = cd.AddCodeID where 1=1 and cra.Status = 2 "
				+ l_cri;
		else
//			query = "select count(rcra.ReceivedId) from RemittanceCashReceivedArchive rcra" +
//					" INNER JOIN AddCode ac ON ac.AddCodeId = rcra.AddCodeID" +
//					l_cri;
		query = "select count(ReceivedId) from RemittanceCashReceivedArchive rcra" +
				" INNER JOIN AddCode ac ON ac.AddCodeId = rcra.AddCodeID and rcra.Status = 2  and IssuedBranchId = "+cri.getFrombranchID()+
				" select count(cra.ReceiptID) from CashRemittanceByAgent_Archive cra" +
				" INNER JOIN CashRemittanceByAgentDetails_Archive cd ON" +
				" cd.ReceiptID = cra.ReceiptID and cra.DefaultBranchID = cd.ToBranchID " +
				" INNER JOIN AddCode ac ON ac.AddCodeId = cd.AddCodeID where 1=1 and cra.Status = 2 and DefaultBranchID ="+cri.getFrombranchID()
				+l_cri;
//		query = "select count(ReceivedId) from RemittanceCashReceivedArchive rcra" +
//				" INNER JOIN AddCode ac ON ac.AddCodeId = rcra.AddCodeID and rcra.Status = 2  and rcra.BranchId="+cri.getFrombranchID()+
//				" select count(cra.ReceiptID) from CashRemittanceByAgent_Archive cra" +
//				" INNER JOIN CashRemittanceByAgentDetails_Archive cd ON" +
//				" cd.ReceiptID = cra.ReceiptID and  cd.ToBranchID="+cri.getFrombranchID()+
//				" INNER JOIN AddCode ac ON ac.AddCodeId = cd.AddCodeID and cra.Status = 2 "
//				+l_cri;
			
			
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public ResultSet prepareCashPaidToIndividualReport(
			CashPaidToIndividualRpt info) throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashPaidToIndividualRpt ?, ?, ?, ?, ?, ?, ? , ? , ? , ?, ?, ? , ? , ?,  ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashPaidToIndividualRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	private static void CashPaidToIndividualRptParameter(CallableStatement l_pstmt,
			CashPaidToIndividualRpt  info) throws SQLException {
		int i = 1;
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getFromDate().getTime()).toString());
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getToDate().getTime()).toString());
		l_pstmt.setString(i++, info.getFrompaymentid());
		l_pstmt.setString(i++, info.getTopaymentid());
		l_pstmt.setString(i++, info.getFromagentid());
		l_pstmt.setString(i++, info.getToagentid());
		l_pstmt.setString(i++, info.getFrombranchid());
		l_pstmt.setString(i++, info.getTobranchid());
		l_pstmt.setString(i++, info.getFromuserid());
		l_pstmt.setString(i++, info.getTouserid());
		l_pstmt.setString(i++, info.getFromcurrencyid());
		l_pstmt.setString(i++, info.getTocurrencyid());
		l_pstmt.setString(i++, info.getFrombankcheque());
		l_pstmt.setString(i++, info.getTobankcheque());
		l_pstmt.setLong(i++, info.getBranchid());
		l_pstmt.setString(i++, info.getOrderby());
	}
	@Override
	public CashPaidToIndividual findpaidamt(long key) throws SQLException {
		CashPaidToIndividual entity = new CashPaidToIndividual();
			query = "select isnull(paidedamount,0)paidedamount  from CashRemittanceByAgentDetails_Archive where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			entity.setPaidedamount(rs.getDouble(field_paidamount));
		rs.close();
		pstmt.close();
		return entity;
	}
	@Override
	public CashPaidToIndividual findpaidamtfrombranch(long key,CashPaidToIndividual info) throws SQLException {
		CashPaidToIndividual entity = new CashPaidToIndividual();
		//if(Status.equals("RcrA"))
		if(info.getReceiptdetailid()==0)
			query = "select isnull(paidedamount,0)paidedamount  from RemittanceCashReceivedArchive where ReceivedId = " + key;
		else
			query = "select isnull(paidedamount,0)paidedamount  from CashRemittanceByAgentDetails_Archive where ReceiptDetailID = " + key;
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			entity.setPaidedamount(rs.getDouble(field_paidamount));
		rs.close();
		pstmt.close();
		return entity;
	}
	
	@Override
	public void updateCashPaid(CashPaidToIndividual info)
			throws SQLException {
		query = updateParam("CashRemittanceByAgentDetails_Archive", new String[] { field_paidamount,field_status}) +
				" AND ReceiptDetailID  = " + info.getReceiptdetailid();
		
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setDouble(i++, info.getPaidedamount());
		pstmt.setByte(i++,info.getP_returnstatus());
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	@Override
	public void UpdateCashPaidfromBranch(CashPaidToIndividual info)
			throws SQLException {
		//if(info.getStatus().equals("RcrA"))
		if(info.getReceiptid()!=0)
			query = updateParam("RemittanceCashReceivedArchive", new String[] { field_paidamount,field_tstatus}) +
				" AND ReceivedId  = " + info.getReceiptid();
		else		
			query =	updateParam("CashRemittanceByAgentDetails_Archive", new String[] { field_paidamount,field_status}) +
				" AND ReceiptDetailID  = " + info.getReceiptdetailid();
				
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setDouble(i++, info.getPaidedamount());
		pstmt.setByte(i++,info.getP_returnstatus());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashPaidToIndividualDataList findCashPaidToIndividualSearchlist(
			CashPaidToIndividualCri cri) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
}


	

